
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/16/2012 14:01:25
-- Generated from EDMX file: C:\Users\mike\Documents\Visual Studio 2010\Projects\MachineQA\MachineQA\QADatabase.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [QADatabase];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_MachinesEnergies]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Energies] DROP CONSTRAINT [FK_MachinesEnergies];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Machines]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Machines];
GO
IF OBJECT_ID(N'[dbo].[Energies]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Energies];
GO
IF OBJECT_ID(N'[dbo].[Modalities]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Modalities];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Machines'
CREATE TABLE [dbo].[Machines] (
    [Id] int  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Energy_Id] int  NOT NULL
);
GO

-- Creating table 'Energies'
CREATE TABLE [dbo].[Energies] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [value] float  NOT NULL
);
GO

-- Creating table 'Modalities'
CREATE TABLE [dbo].[Modalities] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Energy_Id] int  NOT NULL
);
GO

-- Creating table 'Tests'
CREATE TABLE [dbo].[Tests] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Machine_Id] int  NOT NULL
);
GO

-- Creating table 'Measurements'
CREATE TABLE [dbo].[Measurements] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Energy_Id] int  NOT NULL,
    [Test_Id] int  NOT NULL
);
GO

-- Creating table 'PassFailCriterias'
CREATE TABLE [dbo].[PassFailCriterias] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [value] bit  NOT NULL,
    [Measurement_Id] int  NOT NULL
);
GO

-- Creating table 'NumericCriterias'
CREATE TABLE [dbo].[NumericCriterias] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Baseline] float  NOT NULL,
    [LowerBound] float  NOT NULL,
    [UpperBound] nvarchar(max)  NOT NULL,
    [Measurement_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Machines'
ALTER TABLE [dbo].[Machines]
ADD CONSTRAINT [PK_Machines]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Energies'
ALTER TABLE [dbo].[Energies]
ADD CONSTRAINT [PK_Energies]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Modalities'
ALTER TABLE [dbo].[Modalities]
ADD CONSTRAINT [PK_Modalities]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tests'
ALTER TABLE [dbo].[Tests]
ADD CONSTRAINT [PK_Tests]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Measurements'
ALTER TABLE [dbo].[Measurements]
ADD CONSTRAINT [PK_Measurements]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PassFailCriterias'
ALTER TABLE [dbo].[PassFailCriterias]
ADD CONSTRAINT [PK_PassFailCriterias]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'NumericCriterias'
ALTER TABLE [dbo].[NumericCriterias]
ADD CONSTRAINT [PK_NumericCriterias]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Energy_Id] in table 'Measurements'
ALTER TABLE [dbo].[Measurements]
ADD CONSTRAINT [FK_EnergyMeasurement]
    FOREIGN KEY ([Energy_Id])
    REFERENCES [dbo].[Energies]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EnergyMeasurement'
CREATE INDEX [IX_FK_EnergyMeasurement]
ON [dbo].[Measurements]
    ([Energy_Id]);
GO

-- Creating foreign key on [Measurement_Id] in table 'PassFailCriterias'
ALTER TABLE [dbo].[PassFailCriterias]
ADD CONSTRAINT [FK_MeasurementPassFailCriteria]
    FOREIGN KEY ([Measurement_Id])
    REFERENCES [dbo].[Measurements]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MeasurementPassFailCriteria'
CREATE INDEX [IX_FK_MeasurementPassFailCriteria]
ON [dbo].[PassFailCriterias]
    ([Measurement_Id]);
GO

-- Creating foreign key on [Measurement_Id] in table 'NumericCriterias'
ALTER TABLE [dbo].[NumericCriterias]
ADD CONSTRAINT [FK_MeasurementNumericCriteria]
    FOREIGN KEY ([Measurement_Id])
    REFERENCES [dbo].[Measurements]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MeasurementNumericCriteria'
CREATE INDEX [IX_FK_MeasurementNumericCriteria]
ON [dbo].[NumericCriterias]
    ([Measurement_Id]);
GO

-- Creating foreign key on [Test_Id] in table 'Measurements'
ALTER TABLE [dbo].[Measurements]
ADD CONSTRAINT [FK_TestMeasurement]
    FOREIGN KEY ([Test_Id])
    REFERENCES [dbo].[Tests]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TestMeasurement'
CREATE INDEX [IX_FK_TestMeasurement]
ON [dbo].[Measurements]
    ([Test_Id]);
GO

-- Creating foreign key on [Machine_Id] in table 'Tests'
ALTER TABLE [dbo].[Tests]
ADD CONSTRAINT [FK_MachineTest]
    FOREIGN KEY ([Machine_Id])
    REFERENCES [dbo].[Machines]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MachineTest'
CREATE INDEX [IX_FK_MachineTest]
ON [dbo].[Tests]
    ([Machine_Id]);
GO

-- Creating foreign key on [Energy_Id] in table 'Modalities'
ALTER TABLE [dbo].[Modalities]
ADD CONSTRAINT [FK_EnergyModality]
    FOREIGN KEY ([Energy_Id])
    REFERENCES [dbo].[Energies]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EnergyModality'
CREATE INDEX [IX_FK_EnergyModality]
ON [dbo].[Modalities]
    ([Energy_Id]);
GO

-- Creating foreign key on [Energy_Id] in table 'Machines'
ALTER TABLE [dbo].[Machines]
ADD CONSTRAINT [FK_EnergyMachine]
    FOREIGN KEY ([Energy_Id])
    REFERENCES [dbo].[Energies]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EnergyMachine'
CREATE INDEX [IX_FK_EnergyMachine]
ON [dbo].[Machines]
    ([Energy_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------